package com.lyc.util;

import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.Serializable;
import java.lang.annotation.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.StringJoiner;

public class DBUtil {

    //获取数据库连接对象
    private static String url;
    private static String user;
    private static String password;

    static {
        //加载数据库驱动
        try {
            Properties config = new Properties();
            config.load(DBUtil.class.getResourceAsStream("/jdbc.properties"));
            url = config.getProperty("jdbc.url");
            user = config.getProperty("jdbc.user");
            password = config.getProperty("jdbc.password");
            Class.forName(config.getProperty("jdbc.driver", "com.mysql.jdbc.Driver"));
        } catch (Exception ex) {
            ex.printStackTrace();
            System.exit(0);
        }
    }

    private DBUtil() {
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public static void setParam(PreparedStatement pstmt,Object... objs) throws SQLException{
        if(objs==null||objs.length==0){
            return;
        }
        for(int i=0;i<objs.length;i++){
            pstmt.setObject(i+1, objs[i]);
        }
    }

    public static int execute(String sql,Object... objs)throws SQLException {
        try(
                Connection conn = getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            setParam(pstmt, objs);
            return pstmt.executeUpdate();
        }
    }

    public static void close(Connection conn, Statement stmt) {
        try {
            stmt.close();
        } catch (Exception e) {
        }
        try {
            conn.close();
        } catch (Exception e) {
        }
    }

    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            rs.close();
        } catch (Exception e) {
        }
        close(conn, stmt);
    }

    public static <E> E selectOne(String sql,Mapper<E> mapper,Object... args)throws SQLException{
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            setParam(pstmt, args);
            rs = pstmt.executeQuery();
            if(rs.next()){
                return mapper.map(rs);
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            close(conn,pstmt,rs);
        }
    }

    public static <E> E selectOne(String sql,Class<E> cls,Object... args)throws SQLException{
        return selectOne(sql, new EntityLoader<>(cls), args);
    }

    public static <E> List<E> selectList(String sql, Mapper<E> mapper, Object... args)throws SQLException{
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            setParam(pstmt,args);
            rs = pstmt.executeQuery();
            List<E> list = new ArrayList<>();
            while (rs.next()) {
                list.add(mapper.map(rs));
            }
            return list;
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }
    public static <E> List<E> selectList(String sql, Class<E> cls, Object... args)throws SQLException{
        return selectList(sql,new EntityLoader<>(cls),args);
    }

    public static BigDecimal selectBigDecimal(String sql,Object... args)throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            setParam(pstmt,args);
            rs = pstmt.executeQuery();
            if(rs.next()){
                return rs.getBigDecimal(1);
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    public static Object selectObjct(String sql,Object... args)throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            setParam(pstmt,args);
            rs = pstmt.executeQuery();
            if(rs.next()){
                return rs.getObject(1);
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    public static String selectString(String sql,Object... args)throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            setParam(pstmt,args);
            rs = pstmt.executeQuery();
            if(rs.next()){
                return rs.getString(1);
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }


    public static int insert(Object entity) throws SQLException {
        return insert(entity, true);
    }

    public static int insertSelevtion(Object entity) throws SQLException{
        return insert(entity, false);
    }

    private static int insert(Object entity,boolean nullable)throws SQLException {
        if(entity==null){
            throw new SQLException("不能插入一个空对象");
        }
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();

        Class<?> cls = entity.getClass();
        //生成sql
        sql.append("insert into ");
        sql.append(getTableName(cls));

        PropertyDescriptor[] pds = getPropertyDescriptors(cls);//得到所有的属性
        StringJoiner cols = new StringJoiner(",");
        StringJoiner values = new StringJoiner(",");
        for(PropertyDescriptor pd : pds){
            if(exclude(pd)){
                continue;
            }

            Object param = null;//参数值
            try {
                param = pd.getReadMethod().invoke(entity);//param =entity.getXxx();
            } catch (Exception e) {
                throw new SQLException("获取参数值失败:"+pd.getReadMethod().toString(),e);
            }
            if(param==null&&!nullable){
                continue;
            }

            params.add(param);

            cols.add(getColumnName(pd));
            values.add("?");
        }
        sql.append('(');
        sql.append(cols);
        sql.append(") values(");
        sql.append(values);
        sql.append(")");

        return execute(sql.toString(), params.toArray());
    }

    public static int updateById(Object entity,boolean nullable)throws SQLException {
        if(entity==null){
            throw new SQLException("不能插入一个空对象");
        }
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();

        Class<?> cls = entity.getClass();

        sql.append("update ");
        sql.append(getTableName(cls));
        sql.append(" set ");

        StringJoiner cols = new StringJoiner(",");
        String where = null;
        Object idValue = null;
        for(PropertyDescriptor pd : getPropertyDescriptors(cls)){
            if(exclude(pd)){
                continue;
            }

            Object param = null;//参数值
            try {
                param = pd.getReadMethod().invoke(entity);//param =entity.getXxx();
            } catch (Exception e) {
                throw new SQLException("获取参数值失败:"+pd.getReadMethod().toString(),e);
            }
            if(isId(pd)){
                if(param==null){
                    throw new SQLException("实体"+cls.getName()+"."+pd.getName()+"不能为null");
                }
                where = " where "+getColumnName(pd)+"=?";
                idValue = param;
                continue;
            }
            if(param==null&&!nullable){
                continue;
            }

            {
                params.add(param);
                cols.add(getColumnName(pd) + "=?");
            }

        }
        if(where==null){
            throw new SQLException("实体"+cls.getName()+"未定义主键@DBUtil.Id");
        }

        sql.append(cols.toString());
        sql.append(where);
        params.add(idValue);

        return execute(sql.toString(), params.toArray());
    }

    public static int deleteById(Serializable id, Class<?> cls)throws SQLException {
        if(id==null){
            throw new SQLException("主键不能为空");
        }
        if(cls == null){
            throw new SQLException("实体类不能为空");
        }
        StringBuilder sql = new StringBuilder();

        sql.append("delete from ");
        sql.append(getTableName(cls));

        String where = null;
        for(PropertyDescriptor pd : getPropertyDescriptors(cls)){
            if(exclude(pd)){
                continue;
            }

            if(isId(pd)){
                where = " where "+getColumnName(pd)+"=?";
                break;
            }

        }
        if(where==null){
            throw new SQLException("实体"+cls.getName()+"未定义主键@DBUtil.Id");
        }

        sql.append(where);
        return execute(sql.toString(), id);
    }

    public static <E> E selectById(Serializable id, Class<E> cls)throws SQLException {
        if(id==null){
            throw new SQLException("主键不能为空");
        }
        if(cls == null){
            throw new SQLException("实体类不能为空");
        }
        StringBuilder sql = new StringBuilder();

        sql.append("select * from ");
        sql.append(getTableName(cls));

        String where = null;
        for(PropertyDescriptor pd : getPropertyDescriptors(cls)){
            if(exclude(pd)){
                continue;
            }

            if(isId(pd)){
                where = " where "+getColumnName(pd)+"=?";
                break;
            }

        }
        if(where==null){
            throw new SQLException("实体"+cls.getName()+"未定义主键@DBUtil.Id");
        }

        sql.append(where);
        return selectOne(sql.toString(), cls, id);
    }

    public static <E> List<E> selectList(Class<E> cls,int index,int len)throws SQLException {
        if(cls == null){
            throw new SQLException("实体类不能为空");
        }
        StringBuilder sql = new StringBuilder();

        sql.append("select * from ");
        sql.append(getTableName(cls));

        String id = null;
        for(PropertyDescriptor pd : getPropertyDescriptors(cls)){
            if(exclude(pd)){
                continue;
            }
            if(isId(pd)){
                id = getColumnName(pd);
                break;
            }
        }
        if(id!=null) {
            sql.append(" order by ");
            sql.append(id);
            sql.append(" desc");
        }
        sql.append(" limit ?,?");
        return selectList(sql.toString(), cls, index,len);
    }

    public static Long count(Class<?> cls)throws SQLException {
        if(cls == null){
            throw new SQLException("实体类不能为空");
        }
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from ");
        sql.append(getTableName(cls));
        return selectBigDecimal(sql.toString()).longValue();
    }

    public static String getColumnName(PropertyDescriptor pd){

        Method m = pd.getReadMethod();//获取getter
        if(m!=null && m.isAnnotationPresent(Name.class)){
            return m.getAnnotation(Name.class).value();
        }

        //setter上要就没有写@Id
        m = pd.getWriteMethod();//获取setter
        if(m!=null && m.isAnnotationPresent(Name.class)){
            return m.getAnnotation(Name.class).value();
        }

        //获取对应的Field有没有写@Id
        try {
            Field f = pd.getReadMethod().getDeclaringClass().getDeclaredField(pd.getName());
            if(f!=null && f.isAnnotationPresent(Name.class)){
                return f.getAnnotation(Name.class).value();
            }
        }catch (Exception ex){
        }
        String columnName = pd.getName();
        columnName = columnName.replaceAll("([A-Z])", "_$1").toLowerCase();
        return columnName;
    }

    public static boolean exclude(PropertyDescriptor pd){
        return pd.getName().equals("class") || pd.getReadMethod()==null || pd.getWriteMethod()==null;
    }

    public static String getTableName(Class<?> cls){
        String tableName = cls.getSimpleName();
        if(cls.isAnnotationPresent(Name.class)){
            Name name = cls.getAnnotation(Name.class);
            tableName = name.value();
        }else {
            //大驼峰命名规则转换成蛇形命名规则
            tableName = tableName.replaceAll("([A-Z])", "_$1").toLowerCase().substring(1);
        }
        return tableName;
    }

    public static PropertyDescriptor[] getPropertyDescriptors(Class<?> cls)throws SQLException{
        try {
            return Introspector.getBeanInfo(cls).getPropertyDescriptors();
        } catch (IntrospectionException e) {
            throw new SQLException("获取bean的属性失败",e);
        }
    }

    public static boolean isId(PropertyDescriptor pd){
        //getter上要就没有写@Id
        Method m = pd.getReadMethod();//获取getter
        if(m!=null && m.isAnnotationPresent(Id.class)){
            return true;
        }

        //setter上要就没有写@Id
        m = pd.getWriteMethod();//获取setter
        if(m!=null && m.isAnnotationPresent(Id.class)){
            return true;
        }

        //获取对应的Field有没有写@Id
        try {
            Field f = pd.getReadMethod().getDeclaringClass().getDeclaredField(pd.getName());
            if(f!=null && f.isAnnotationPresent(Id.class)){
                return true;
            }
        }catch (Exception ex){

        }
        return false;
    }

    public static interface Mapper<E> {
        E map(ResultSet rs)throws SQLException;
    }

    private static class EntityLoader<T> implements Mapper<T> {

        private Class<T> cls;

        public EntityLoader(Class<T> cls) {
            this.cls = cls;
        }

        @Override
        public T map(ResultSet rs) throws SQLException {
            T obj = null;
            try {
                obj = cls.newInstance();
            } catch (Exception e) {
                throw new SQLException("调用无参构造方法实例化错误：" + cls.getName(), e);
            }

            for (PropertyDescriptor pd : getPropertyDescriptors(cls)) {
                if (exclude(pd)) {
                    continue;
                }
                String columnName = getColumnName(pd);

                try {
                    Object value = null;
                    try {
                        value = rs.getObject(columnName);
                    } catch (Exception ex) {
                        // 警告，字段不存在
                    }
                    if (value != null) {
                        if (pd.getPropertyType() == Integer.class) {
                            value = rs.getInt(columnName);
                        } else if (pd.getPropertyType() == Character.class) {
                            value = rs.getString(columnName).charAt(0);
                        } else if (pd.getPropertyType() == java.util.Date.class) {
                            value = new java.util.Date(rs.getTimestamp(columnName).getTime());
                        } else {
                            try {
                                value = ResultSet.class
                                        .getMethod("get" + pd.getPropertyType().getSimpleName(), String.class)
                                        .invoke(rs, columnName);
                            } catch (Exception ex) {
                            }
                        }
                        pd.getWriteMethod().invoke(obj, value);
                    }

                } catch (Exception e) {
                    throw new SQLException("设置字段错误：" + columnName, e);
                }
            }
            return obj;
        }
    }

    public static interface BaseDAO<E> {

        int insert(E entity)throws SQLException;

        int updateById(E entity)throws SQLException;

        int deleteById(Serializable id)throws SQLException;

        E selectById(Serializable id)throws SQLException;

        List<E> select(int index,int len)throws SQLException;

        long count()throws SQLException;
    }

    public static class BaseDAOImpl<E> implements BaseDAO<E> {

        private Class<E> cls;

        public BaseDAOImpl(){
            cls = (Class<E>)((ParameterizedType)getClass().getGenericSuperclass()).getActualTypeArguments()[0];
        }

        @Override
        public int insert(E entity) throws SQLException {
            return DBUtil.insertSelevtion(entity);
        }

        @Override
        public int updateById(E entity) throws SQLException {
            return DBUtil.updateById(entity,true);
        }

        @Override
        public int deleteById(Serializable id) throws SQLException {
            return DBUtil.deleteById(id,cls);
        }

        @Override
        public E selectById(Serializable id) throws SQLException {
            return DBUtil.selectById(id,cls);
        }

        @Override
        public List<E> select(int index, int len) throws SQLException {
            return DBUtil.selectList(cls,index,len);
        }

        @Override
        public long count() throws SQLException {
            return DBUtil.count(cls);
        }
    }

    @Target({ElementType.METHOD,ElementType.FIELD})
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    @Inherited
    public static @interface Id{

    }

    @Target({ElementType.METHOD,ElementType.FIELD,ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    @Inherited
    public static @interface Name {
        String value();
    }
}
